package cn.sfa.service.role;

import java.sql.ResultSet;
import java.sql.SQLException;

import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.sql.rowset.serial.SerialException;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.RowCallbackHandler;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import cn.sfa.cache.CacheFactory;
import cn.sfa.cache.ICacheHelper;
import cn.sfa.common.pagination.Pagination;
import cn.sfa.common.utils.CommonUtil;
import cn.sfa.common.utils.MajorKeyGenerate;
import cn.sfa.common.utils.TableNameEnum;
import cn.sfa.entity.affixItem.SaleAffixItem;
import cn.sfa.entity.role.SaleModule;
import cn.sfa.entity.role.SaleRole;
import cn.sfa.entity.role.SaleUserRole;


/**
 * 角色管理服务类
 * @ClassName：RoleService 
 * @Description：TODO
 * @author：LiXiang
 * @date：2012-7-4 上午10:00:28 
 *
 */
@Service
@Transactional
public class RoleService {
	
	Logger logger = LoggerFactory.getLogger(RoleService.class);
	ICacheHelper  cacheHelper = CacheFactory.CreateCacheHelper();
	
	@Autowired
	NamedParameterJdbcTemplate jdbcTemplate;
	
	@Autowired
	MajorKeyGenerate majorKeyGenerate;
	
	/**
	 * 查询所有模块
	 * TODO
	 * @author: LiXiang
	 * @return
	 */
	public List<SaleModule> getModule(String id){
		String sql="select * from salemodule s where s.parentmoduleid = :PARENTMODULEID";
		
		Map<String,Object> paramMap = new HashMap<String,Object>();
		paramMap.put("PARENTMODULEID", id);
		
		List<SaleModule> list = jdbcTemplate.query(sql,paramMap,new BeanPropertyRowMapper(SaleModule.class));
		return list;
	}
	
	/**
	 * 查询所有角色
	 * TODO
	 * @author: LiXiang
	 * @param roleName
	 * @param currentPage
	 * @param numPerPage
	 * @param url
	 * @return
	 */
	public Pagination getRole(String roleName,int currentPage,//页码
			int numPerPage,//页尺寸
			String url){
		String sql="select * from salerole a where 1=1 ";
		Map<String,Object> paramMap = new HashMap<String,Object>();
		if(!"".equals(roleName)&&roleName != null&&!"null".equals(roleName)){
			sql += " and a.rolename like :roleName";
			paramMap.put("roleName", "%"+roleName+"%");
		}
		
		return new Pagination(sql,paramMap,currentPage,numPerPage,url,jdbcTemplate);
	}
	
	/**
	 * 根据ID查询角色
	 * TODO
	 * @author: LiXiang
	 * @param id
	 * @return
	 */
	public SaleRole getRoleById(String id){
		String sql="select * from salerole a where saleroleid = :saleroleid ";
		Map<String,Object> paramMap = new HashMap<String,Object>();
		paramMap.put("saleroleid", id);
		
		final SaleRole saleRole=new SaleRole();
		jdbcTemplate.query(sql, paramMap, new RowCallbackHandler() {
			public void processRow(ResultSet rs) throws SQLException {
				saleRole.setSaleRoleId(rs.getString("saleroleid"));
				saleRole.setIsBaseRole(rs.getString("isbaserole"));
				saleRole.setRoleContent(rs.getString("rolecontent"));
				saleRole.setRoleStatus(rs.getString("rolestatus"));
				saleRole.setRoleName(rs.getString("rolename"));
				saleRole.setCreateUid(rs.getString("createuserid"));
				saleRole.setUpdateId(rs.getString("updateuserid"));
				
				saleRole.setCreateDate(rs.getDate("createdate"));
				saleRole.setUpdateDate(rs.getDate("updatedate"));
			}
		});
		return saleRole;
	}
	
	/**
	 * 根据Name查询角色
	 * TODO
	 * @author: LiXiang
	 * @param id
	 * @return
	 */
	public SaleRole getRoleByName(String name){
		String sql="select * from salerole a where rolename = :name ";
		Map<String,Object> paramMap = new HashMap<String,Object>();
		paramMap.put("name", name);
		
		final SaleRole saleRole=new SaleRole();
		jdbcTemplate.query(sql, paramMap, new RowCallbackHandler() {
			public void processRow(ResultSet rs) throws SQLException {
				saleRole.setSaleRoleId(rs.getString("saleroleid"));
				saleRole.setIsBaseRole(rs.getString("isbaserole"));
				saleRole.setRoleContent(rs.getString("rolecontent"));
				saleRole.setRoleStatus(rs.getString("rolestatus"));
				saleRole.setRoleName(rs.getString("rolename"));
				saleRole.setCreateUid(rs.getString("createuserid"));
				saleRole.setUpdateId(rs.getString("updateuserid"));
				
				saleRole.setCreateDate(rs.getDate("createdate"));
				saleRole.setUpdateDate(rs.getDate("updatedate"));
			}
		});
		return saleRole;
	}
	
	/**
	 * 查询角色包含的所有模块
	 * TODO
	 * @author: LiXiang
	 * @param id
	 * @return
	 */
	public List getRoleMoudleById(String id){
		String sql = "select b.saleroleid, c.moduleid, c.modulelevel, c.parentmoduleid "
				+ "from salerolemodule a, salerole b, salemodule c "
				+ " where a.saleroleid = b.saleroleid "
				+ "and a.salemoduleid = c.moduleid "
				+ "and a.saleroleid = :saleroleid "
				+ "and c.moduleid not in "
				+ "(select c.parentmoduleid "
				+ " from salerolemodule a, salerole b, salemodule c "
				+ " where a.saleroleid = b.saleroleid "
				+ " and a.salemoduleid = c.moduleid "
				+ " and a.saleroleid = :saleroleid) ";
		Map<String,Object> paramMap = new HashMap<String,Object>();
		paramMap.put("saleroleid", id);
		
		List list = jdbcTemplate.queryForList(sql, paramMap);
		
		return list;
	}
	
	/**
	 * 新建角色类型
	 * TODO
	 * @author: LiXiang
	 * @param saleRole
	 * @return
	 */
	public String addRole(SaleRole saleRole){
		String sql="insert into salerole(saleroleid,createdate,createuserid,updatedate,updateuserid,rolename,isbaserole,rolestatus,rolecontent) " +
				"values(default,:createdate,:createuserid,:updatedate,:updateuserid,:rolename,:isbaserole,:rolestatus,:rolecontent)";
		
		Map<String,Object> paramMap = new HashMap<String,Object>();
		paramMap.put("createdate", saleRole.getCreateDate());
		paramMap.put("createuserid", saleRole.getCreateUid());
		paramMap.put("updatedate", saleRole.getCreateDate());
		paramMap.put("updateuserid", saleRole.getCreateUid());
		paramMap.put("rolename", saleRole.getRoleName());
		paramMap.put("isbaserole", saleRole.getIsBaseRole());
		paramMap.put("rolestatus", saleRole.getRoleStatus());
		paramMap.put("rolecontent", saleRole.getRoleContent());
		
		int count=jdbcTemplate.update(sql,paramMap);
		if(count>0){
			saleRole = getRoleByName(saleRole.getRoleName());
			return saleRole.getSaleRoleId();
		}
		return null;
	}
	
	/**
	 * 修改角色
	 * TODO
	 * @author: LiXiang
	 * @param saleRole
	 * @return
	 */
	public boolean updateRole(SaleRole saleRole){
		String sqlStr = "update salerole set " +
				"updatedate = :updatedate, " +
				"updateuserid = :updateuserid, " +
				"rolename = :rolename, " +
				"isbaserole = :isbaserole, " +
				"rolestatus = :rolestatus, " +
				"rolecontent = :rolecontent " +
				"where saleroleid = :saleroleid ";
		Map<String,Object> paramMap = new HashMap<String,Object>();
		paramMap.put("updatedate", saleRole.getUpdateDate());
		paramMap.put("updateuserid", CommonUtil.jugeAndFixNull(saleRole.getUpdateId()));
		paramMap.put("rolename", CommonUtil.jugeAndFixNull(saleRole.getRoleName()));
		paramMap.put("isbaserole", saleRole.getIsBaseRole()); 
		paramMap.put("rolestatus", saleRole.getRoleStatus()); 
		paramMap.put("rolecontent", saleRole.getRoleContent()); 
		paramMap.put("saleroleid", saleRole.getSaleRoleId()); 
		  
		return jdbcTemplate.update(sqlStr,paramMap)>0;
	}
	
	/**
	 * 修改角色对应模块信息
	 * TODO
	 * @author: LiXiang
	 * @param saleRole
	 * @return
	 */
	public boolean updateRoleForMoudle(SaleRole saleRole){
		//step1:删除当前角色对应所有模块
		
		//step2:新增角色对应模块
		
		
		String sqlStr = "update salerole set " +
				"updatedate = :updatedate, " +
				"updateuserid = :updateuserid, " +
				"rolename = :rolename " +
				"isbaserole = :isbaserole " +
				"rolestatus = :rolestatus " +
				"rolecontent = :rolecontent " +
				"where saleroleid = :saleroleid ";
		Map<String,Object> paramMap = new HashMap<String,Object>();
		paramMap.put("updatedate", saleRole.getUpdateDate());
		paramMap.put("updateuserid", CommonUtil.jugeAndFixNull(saleRole.getUpdateId()));
		paramMap.put("rolename", CommonUtil.jugeAndFixNull(saleRole.getRoleName()));
		paramMap.put("isbaserole", saleRole.getIsBaseRole()); 
		paramMap.put("rolestatus", saleRole.getRoleStatus()); 
		paramMap.put("rolecontent", saleRole.getRoleContent()); 
		paramMap.put("saleroleid", saleRole.getSaleRoleId()); 
		  
		return jdbcTemplate.update(sqlStr,paramMap)>0;
	}
	
	
	/**
	 * 新建角色与模块关联
	 * TODO
	 * @author: LiXiang
	 * @param addRoleModule
	 * @return
	 */
	public boolean addRoleModule(String roleId,String moduleId,Date createDate,String createUserId){
		String sql="insert into salerolemodule(recordid,createdate,createuserid,updatedate,updateuserid,saleroleid,salemoduleid) " +
				"values(default,:createdate,:createuserid,:updatedate,:updateuserid,:saleroleid,:salemoduleid)";
		
		Map<String,Object> paramMap = new HashMap<String,Object>();
		paramMap.put("createdate", createDate);
		paramMap.put("createuserid", createUserId);
		paramMap.put("updatedate", createDate);
		paramMap.put("updateuserid", createUserId);
		paramMap.put("saleroleid", roleId);
		paramMap.put("salemoduleid",moduleId);
		
		int count=jdbcTemplate.update(sql,paramMap);
		if(count>0)
			return true;
		return false;
	}
	
	/**
	 * 删除
	 * TODO
	 * @author: LiXiang
	 * @param id
	 * @return
	 */
	public boolean deleteRoleById(String id) {
		boolean isSuccess = false;
		Map<String, Object> map = new HashMap<String, Object>();
		map.put("roleid", id);

		// step1:删除角色和用户对应表中的数据
		String sql = "delete from saleuserrole where roleid=:roleid";
		int count = jdbcTemplate.update(sql, map);
		if (count > 0) {
			isSuccess = true;
		}

		// step2:删除角色和模块对应表中的数据
		isSuccess = this.deleteRoleModuleById(id);

		// step3:删除角色
		sql = "delete from salerole where saleroleid=:roleid";
		count = jdbcTemplate.update(sql, map);
		if (count > 0) {
			isSuccess = true;
		}

		return isSuccess;
	}
	
	/**
	 * 角色和模块对应表中的数据
	 * TODO
	 * @author: LiXiang
	 * @param id
	 * @return
	 */
	public boolean deleteRoleModuleById(String id) {
		boolean isSuccess = false;

		// 删除角色和模块对应表中的数据
		String sql = "delete from salerolemodule where saleroleid=:roleid";
		Map<String, Object> map = new HashMap<String, Object>();
		map.put("roleid", id);
		int count = jdbcTemplate.update(sql, map);
		if (count > 0) {
			isSuccess = true;
		}
		return isSuccess;
	}
	
	
	/**
	 * 查询所有角色,不分页
	 * TODO
	 * @author: LiXiang
	 * @param roleName
	 * @param currentPage
	 * @param numPerPage
	 * @param url
	 * @return
	 */
	public List getRoles(){
		String sql="select * from salerole a ";
		 
		Map<String,Object> paramMap = new HashMap<String,Object>();
		return jdbcTemplate.queryForList(sql, paramMap);
	}
	
	
	/**
	 * 查询用户已有的角色
	 * TODO
	 * @author: LiXiang
	 * @param roleName
	 * @param currentPage
	 * @param numPerPage
	 * @param url
	 * @return
	 */
	public List getRoles(String id){
		String sql="select ur.roleid from saleuserrole ur "+
					" where ur.userid=:id";
		 
		Map<String,Object> paramMap = new HashMap<String,Object>();
		paramMap.put("id", id);
		return jdbcTemplate.queryForList(sql, paramMap);
	}
	
	
	
	/**
	 * 编辑用户角色
	 * @param saleNews
	 * @return boolean
	 * @throws SQLException 
	 * @throws SerialException 
	 */
	public boolean updateRoles(SaleUserRole saleUserRole)
	{	
		 
			String sql="delete from saleuserrole where userid=:userId";
			Map<String,Object> paramMap=new HashMap<String,Object>();
			paramMap.put("userId", saleUserRole.getUserId());
			jdbcTemplate.update(sql, paramMap);
			
			sql="insert into saleuserrole(recordid,createdate,createuserid,"+
			"updatedate,updateuserid,userid,roleid) values(default,:createdate,:createuserid,"+
			":updatedate,:updateuserid,:userid,:roleid)";
			Map[] batchValues=new HashMap[saleUserRole.getRoleId().length];
			for(int i=0;i<saleUserRole.getRoleId().length;i++)
			{
				Map<String,Object> map=new HashMap<String,Object>();
				map.put("createdate", saleUserRole.getCreateDate());
				map.put("createuserid", saleUserRole.getCreateUid());
				map.put("updatedate", saleUserRole.getUpdateDate());
				map.put("updateuserid", saleUserRole.getUpdateId());
				map.put("userid", saleUserRole.getUserId());
				map.put("roleid", saleUserRole.getRoleId()[i]);
				batchValues[i]=map;
			}
			int[] count=jdbcTemplate.batchUpdate(sql, batchValues);
			if(count.length>0)
			return true;
		
		return false;
	}
}
